﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ACC.Extensions.Utilities
{
    public class SqlHelper
    {
        public SqlHelper()
        {
            //TODO:构造数据库常用操作方法集
        }
        private static SqlConnection connection;
        private static string connectionString;
        public static SqlConnection Connection
        {
            get
            {
                System.Configuration.Configuration config = System.Configuration.ConfigurationManager.OpenExeConfiguration(Application.ExecutablePath);
                connectionString = config.ConnectionStrings.ConnectionStrings["connectionString"].ToString(); //使用范例..
                try
                {
                    if (connection == null)
                    {
                        connection = new SqlConnection(connectionString);

                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                }
                catch (Exception ex)
                {
                    Log4Helper.Error(ex.Message);
                    throw (ex);
                }
                return connection;
            }
        }

        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        /// <summary>
        /// 批量插入ACC_PLCData
        /// </summary>
        /// <param name="dt">数据源</param>
        public static void TableValuedToDB(DataTable dt)
        {
            const string TSqlStatement =
                  "INSERT INTO ACC_PLCData (Station,ID,DBAddress,DBDesc,DBValue,DT)" +
                  " SELECT nc.Station, nc.ID, nc.DBAddress, nc.DBDesc, nc.DBValue,nc.DT" +
                  " FROM @NewACC_PLCDataTvp AS nc";
            SqlCommand cmd = new SqlCommand(TSqlStatement, Connection);
            SqlParameter catParam = cmd.Parameters.AddWithValue("@NewACC_PLCDataTvp", dt);
            catParam.SqlDbType = SqlDbType.Structured;
            catParam.TypeName = "dbo.ACC_PLCDataTvp";
            try
            {
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("error>" + ex.Message);
            }
        }

        /// <summary>
        /// 批量插入ACC_REALTIME_DATA
        /// </summary>
        /// <param name="dt">数据源</param>
        public static void TableValuedToRealTimeTable(DataTable dt)
        {
            const string TSqlStatement =
                  "INSERT INTO ACC_REALTIME_DATA (ID,LINE,OP,STN,PARTNO,CTIME,SHIFT,CYCLETIME,WAITINGTIME,INJECTIONTIME,OvenTemp)" +
                  " SELECT nc.ID, nc.LINE, nc.OP, nc.STN, nc.PARTNO,nc.CTIME,nc.SHIFT,nc.CYCLETIME,nc.WAITINGTIME,nc.INJECTIONTIME,nc.OvenTemp" +
                  " FROM @NewACC_REALTIME_DATATvp AS nc";
            SqlCommand cmd = new SqlCommand(TSqlStatement, Connection);
            SqlParameter catParam = cmd.Parameters.AddWithValue("@NewACC_REALTIME_DATATvp", dt);
            catParam.SqlDbType = SqlDbType.Structured;
            catParam.TypeName = "dbo.ACC_REALTIME_DATATvp";
            try
            {
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("error>" + ex.Message);
            }
        }

        /// <summary>
        /// 批量插入ACC_CNC_SW_WORKCOUNT
        /// </summary>
        /// <param name="dt">数据源</param>
        public static void TableValuedToWorkCountTable(DataTable dt)
        {
            const string TSqlStatement =
                  "INSERT INTO ACC_CNC_SW_WORKCOUNT (ID,LINE,OP,STN,PARTNO,CTIME,SHIFT,WORKCOUNT)" +
                  " SELECT nc.ID, nc.LINE, nc.OP, nc.STN, nc.PARTNO,nc.CTIME,nc.SHIFT,nc.WORKCOUNT" +
                  " FROM @NewACC_CNC_SW_WORKCOUNTTvp AS nc";
            SqlCommand cmd = new SqlCommand(TSqlStatement, Connection);
            SqlParameter catParam = cmd.Parameters.AddWithValue("@NewACC_CNC_SW_WORKCOUNTTvp", dt);
            catParam.SqlDbType = SqlDbType.Structured;
            catParam.TypeName = "dbo.ACC_CNC_SW_WORKCOUNTTvp";
            try
            {
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("error>" + ex.Message);
            }
        }

        /// <summary>
        /// 带参数的执行命令
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <param name="values">返回VALUE值</param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 返回影响记录数
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static DataTable GetDataTable(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataTable(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        public static DataSet GetDataSet(string sql, string table)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds, table);
            return ds;
        }

        public static DataSet GetDataSet(string sql, string table, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds, table);
            return ds;
        }
    }
}
